﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace 建设项目数据爬取
{
    internal class DatabaseTool
    {
        
        private static DatabaseTool Instance;
        private static readonly object padlock = new object();
        public static MySqlConnection connection { get; set; }
        /// <summary>
        /// 获取单例DatabaseTool
        /// </summary>
        /// <returns>单例DatabaseTool对象</returns>
        public static DatabaseTool GetInstance()
        {
            lock (padlock)
            {
                if (Instance == null)
                {
                    Instance = new DatabaseTool();
                }
                return Instance;
            }
        }

        public ConnectionState OpenDatabase(string DatabaseName)
        {
            string connectSQL = string.Format("server=127.0.0.1;port=3306;user=root;password=----;database='{0}';",DatabaseName);
            connection = new MySqlConnection(connectSQL);
            if (connection.State == System.Data.ConnectionState.Closed)
                connection.Open();
            if (connection.State == System.Data.ConnectionState.Open)
                Debug.WriteLine("数据库【"+ DatabaseName + "】打开成功");
            else
                Debug.WriteLine("数据库【"+ DatabaseName + "】打开失败");
            return connection.State;
        }

        public static void CreateTables()
        {
            DatabaseTool.GetInstance().OpenDatabase("xmjs");
            //创建xpath表
            string CreateXpathTableSQL = "CREATE TABLE IF NOT EXISTS Xpaths(id int PRIMARY KEY AUTO_INCREMENT,cityName VARCHAR(100) NOT NULL,url VARCHAR(200), indexXpath VARCHAR(100) NOT NULL,detailTitleXpath VARCHAR(100) NOT NULL, publishTimeXpath VARCHAR(100));";
            MySqlCommand command = new MySqlCommand(CreateXpathTableSQL, DatabaseTool.connection);
            int count = command.ExecuteNonQuery();
            if (count == 0)
                Debug.WriteLine("Xpath表创建成功!");
            else
                Debug.WriteLine("Xpath表创建失败!");
            //创建附件信息表
            string CreateAttachmentTableSQL = "CREATE TABLE IF NOT EXISTS xmjstable(id int PRIMARY KEY AUTO_INCREMENT,district VARCHAR(100),detailTitle VARCHAR(200), fileTitle VARCHAR(100),fileLink VARCHAR(100), publishTime VARCHAR(100),createTime VARCHAR(100));";
            MySqlCommand command1 = new MySqlCommand(CreateAttachmentTableSQL, DatabaseTool.connection);
            int count1 = command1.ExecuteNonQuery();
            if (count1 == 0)
                Debug.WriteLine("附件信息表创建成功!");
            else
                Debug.WriteLine("附件信息表创建失败!");

            DatabaseTool.GetInstance().CloseDatabase("xmjs");
        }

        public void DeleteTable(string TableName)
        {
            string DeleteSQL = string.Format("DROP TABLE '{0}';",TableName);
            DatabaseTool.connection.Open();
            MySqlCommand command = new MySqlCommand(DeleteSQL);
            int count = command.ExecuteNonQuery();
            if (count == 0) 
            {
                Debug.WriteLine("删除数据库表【" + TableName + "】成功");
            }
            else
            {
                Debug.WriteLine("删除数据库表【" + TableName + "】失败");
            }
        }
        /// <summary>
        /// 关闭数据库
        /// </summary>
        /// <param name="DatabaseName">数据库名称</param>
        public void CloseDatabase(string DatabaseName) 
        {
            Debug.WriteLine("关闭数据库【" + DatabaseName + "】");
            connection.Close();
        }

        public void DeleteFrom(string DeleteSQL)
        {
            MySqlCommand command = new MySqlCommand(DeleteSQL,connection);
            int count = command.ExecuteNonQuery();
            if (count == 0) 
            {
                Debug.WriteLine("删除数据失败");
            }
            else
            {
                Debug.WriteLine("删除数据成功");
            }
        }

    }
}
